use eBilling
go


ALTER TABLE COLUMN_DEFINITION ADD DISPLAY_IDX int NULL;
go

ALTER TABLE FORMAT_DEFINITION ADD FORMAT_TYPE int NOT NULL DEFAULT 1;
ALTER TABLE FORMAT_DEFINITION ADD BATCH_USED tinyint NOT NULL DEFAULT 0;
go
ALTER TABLE APP_GROUP ADD CONTACT_NAME VARCHAR(50) NULL;
ALTER TABLE APP_GROUP ADD CONTACT_EMAIL VARCHAR(100) NULL;
ALTER TABLE APP_GROUP ADD CONTACT_TELNO VARCHAR(30) NULL;
ALTER TABLE APP_GROUP ADD CONTACT_CELLNO VARCHAR(30) NULL;
ALTER TABLE APP_GROUP ADD GROUP_ADDRESS VARCHAR(250) NULL;
ALTER TABLE APP_GROUP ADD GROUP_NOTE VARCHAR(100) NULL;
ALTER TABLE APP_GROUP ADD MAIN_BANK VARCHAR(255) NULL;
go

ALTER TABLE REAL_ACCOUNT ADD PARTNER_NO VARCHAR(20) NULL;
go

ALTER TABLE PAYMENT ADD PAYER_EMAIL VARCHAR(100) NULL;
ALTER TABLE PAYMENT ADD PAYER_TELNO VARCHAR(20) NULL;
ALTER TABLE PAYMENT ADD PAYER_CELLNO VARCHAR(20) NULL;
ALTER TABLE PAYMENT ADD PERIOD VARCHAR(50) NULL;
ALTER TABLE PAYMENT ADD PAYMENT_NOTE_1 VARCHAR(500) NULL;
ALTER TABLE PAYMENT ADD PAYMENT_NOTE_2 VARCHAR(500) NULL;
ALTER TABLE PAYMENT ADD PAYMENT_NOTE_3 VARCHAR(255) NULL;
ALTER TABLE PAYMENT ADD MAIN_BANK VARCHAR(255) NULL;
go


ALTER TABLE COMMISSION_CODE_TABLE ADD PAYER_AMOUNT Numeric(19,0)  NULL;
go

ALTER TABLE UPLOAD_FILE ALTER COLUMN PDF_FILE_NAME VARCHAR(100) NULL;
ALTER TABLE UPLOAD_FILE ALTER COLUMN PDF_FILE_PATH_ON_SERVER VARCHAR(255) NULL;
go

ALTER TABLE FEEDBACK_LOG ADD REASON_NOTE VARCHAR(200) NULL;
go


ALTER TABLE WRITE_OFF_PAYMENT ADD TX_DATE Datetime NULL;
ALTER TABLE WRITE_OFF_PAYMENT ADD WRITE_OFF_NOTE VARCHAR(200) NULL;
go

Create table [PAYER]
(
    [PAYER_PK] Numeric(19,0) Identity(1,1) NOT NULL,
    [UPLOAD_FILE_PK] Numeric(19,0) NULL,
    [GROUP_PK] Numeric(19,0) NOT NULL,
    [PAYER_NO] Varchar(20) NOT NULL,
    [PAYER_NAME] Varchar(20) NULL,    
    [PAYER_EMAIL] Varchar(100) NULL,    
    [PAYER_TELNO] Varchar(20) NULL,
    [PAYER_CELLNO] Varchar(20) NULL,   
    [LEVEL_GROUND] Numeric(19,4) NULL,
    [PARKING_COUNT] Integer NULL,
    [HOUSE_NO] Varchar(200) NULL,
    [PAYER_ADDRESS] Varchar(255) NULL,
    [PAYER_MEMO] Varchar(255) NULL, 
    [CREATED_BY] Varchar(50) NULL,
    [CREATED_DATE] Datetime NULL,
    [UPDATE_BY] Varchar(50) NULL,
    [UPDATE_DATE] Datetime NULL,
Primary Key ([PAYER_PK])
) 
go

Create table [PAYER_TEMP]
(
    [PAYER_TEMP_PK] Numeric(19,0) Identity(1,1) NOT NULL,
    [UPLOAD_FILE_PK] Numeric(19,0) NULL,
    [GROUP_PK] Numeric(19,0) NOT NULL,
    [PAYER_NO] Varchar(20) NOT NULL,
    [PAYER_NAME] Varchar(20) NULL,    
    [PAYER_EMAIL] Varchar(100) NULL,    
    [PAYER_TELNO] Varchar(20) NULL,
    [PAYER_CELLNO] Varchar(20) NULL,
    [PAYER_STATUS] Varchar(1) NULL,      
    [RECORD_STATUS] Varchar(1) NULL,  
    [LEVEL_GROUND] Numeric(19,4) NULL,
    [PARKING_COUNT] Integer NULL,
    [HOUSE_NO] Varchar(200) NULL,
    [PAYER_ADDRESS] Varchar(255) NULL,
    [PAYER_MEMO] Varchar(255) NULL, 
    [CONFIRM_DATE] Datetime NULL,
    [CONFIRM_USER_PK] Numeric(19,0) NULL,    
    [CREATED_BY] Varchar(50) NULL,
    [CREATED_DATE] Datetime NULL,
    [UPDATE_BY] Varchar(50) NULL,
    [UPDATE_DATE] Datetime NULL,
Primary Key ([PAYER_TEMP_PK])
) 
go

Create table [PAYMENT_CONFIG]
(
    [PAYMENT_CONFIG_PK] Numeric(19,0) Identity(1,1) NOT NULL,
    [GROUP_PK] Numeric(19,0) NOT NULL,
    [CONFIG_NO] Varchar(20) NOT NULL,
    [CONFIG_NAME] Varchar(50) NOT NULL, 
    [CYCLE_TYPE] Integer NOT NULL, 
    [OFFER_AMOUNT] Numeric(19,0) NULL,
    [NEXT_EXCUTE_TIME] Datetime NULL,
    [LIMIT_DAYS] Integer NOT NULL,
    [PAY_DUE] Datetime NULL,
    [CONFIG_NOTE] Varchar(255) NULL,
    [CREATED_BY] Varchar(50) NULL,
    [CREATED_DATE] Datetime NULL,
    [UPDATE_BY] Varchar(50) NULL,
    [UPDATE_DATE] Datetime NULL,
Primary Key ([PAYMENT_CONFIG_PK])
) 
go

Alter table [PAYMENT_CONFIG] add  foreign key([GROUP_PK]) references [APP_GROUP] ([GROUP_PK])  on update no action on delete no action 
go

Create table [PAYMENT_CONFIG_ITEM]
(
	[PAYMENT_CONFIG_ITEM_PK] Numeric(19,0) Identity NOT NULL,
	[PAYMENT_CONFIG_PK] Numeric(19,0) NOT NULL,
	[ITEM_NAME] Varchar(255) NOT NULL,
	[CONFIG_TYPE] Integer NOT NULL,
	[AMOUNT] Numeric(19,0) NOT NULL,
	[CREATED_BY] Varchar(50) NULL,
	[UPDATE_DATE] Datetime NULL,
	[UPDATE_BY] Varchar(50) NULL,
	[CREATED_DATE] Datetime NULL,
Primary Key ([PAYMENT_CONFIG_ITEM_PK])
) 
go
Alter table [PAYMENT_CONFIG_ITEM] add  foreign key([PAYMENT_CONFIG_PK]) references [PAYMENT_CONFIG] ([PAYMENT_CONFIG_PK])  on update no action on delete no action
go

Create table [WEB_ATM_LOG]
(
    [WEB_ATM_LOG_PK] Numeric(19,0) Identity(1,1) NOT NULL,
    [SYS_DATE_TIME] Varchar(50) NULL,
    [CHANNEL_TYPE] Varchar(50) NULL,
    [CANCEL_NO] Varchar(50) NULL,
    [TRN_ACCT] Varchar(50) NULL,
    [TX_AMT] Varchar(50) NULL,
    [HANDLE_CHARGE] Varchar(50) NULL,
    [WINNER_NUM] Varchar(50) NULL,
    [PROCESS_STATUS] integer NULL,
    [LOG_NOTE] Varchar(50) NULL,    
    [CREATED_DATE] Datetime NULL
Primary Key ([WEB_ATM_LOG_PK])
) 
go


update menu_item
set action_name = '查詢作業'
, IS_ENABLED = 1
, IS_SCHOOL = 1
, IS_COMMITTEE =1
where MENU_ID = 'SOEZ5000';

-- 其他功能
update menu_item
set IS_ENABLED = 0
, IS_SCHOOL = 0
, IS_COMMITTEE =0
where MENU_ID = 'SOEZO000';

--繳款人資料整批上傳
update menu_item
set IS_ENABLED = 1
, IS_SCHOOL = 0
, IS_COMMITTEE =1
where action_page = '/Soez1011.action';

--繳款人資料查詢/修改
update menu_item
set action_name = '繳款人資料查詢/修改'
, IS_ENABLED = 1
, IS_SCHOOL = 0
, IS_COMMITTEE =1
where action_page = '/Soez1015.action';

--繳費資料查詢/修改
update menu_item
set action_name = '繳費資料查詢/修改'
, IS_ENABLED = 1
, IS_SCHOOL = 0
, IS_COMMITTEE =1
where action_page = '/Soez1019.action';

--繳款人資料單筆放行
update menu_item
set IS_ENABLED = 1
, IS_SCHOOL = 0
, IS_COMMITTEE =1
where action_page = '/Soez2001.action';

--繳款人資料上傳放行
update menu_item
set IS_ENABLED = 1
, IS_SCHOOL = 0
, IS_COMMITTEE =1
where action_page = '/Soez2002.action';


--
update menu_item
set action_name = '人工核銷明細查詢'
, IS_ENABLED = 1
, IS_SCHOOL = 1
, IS_COMMITTEE =1
where action_page = '/Soez5001.action';

update menu_item
set action_name = '繳款狀況明細查詢'
, IS_ENABLED = 1
, IS_SCHOOL = 1
, IS_COMMITTEE =1
where action_page = '/Soez5002.action';

update menu_item
set action_name = '帳單通路統計查詢(日)'
, IS_ENABLED = 1
, IS_SCHOOL = 1
, IS_COMMITTEE =1
where action_page = '/Soez5003.action';

update menu_item
set action_name = '帳單通路統計查詢(月)'
, IS_ENABLED = 1
, IS_SCHOOL = 1
, IS_COMMITTEE =1
where action_page = '/Soez5004.action';

update menu_item
set action_name = '帳單通路統計查詢'
, IS_ENABLED = 1
, IS_SCHOOL = 0
, IS_COMMITTEE =0
where action_page = '/Soez5005.action';

-- 交易日誌查詢
update menu_item
set IS_ENABLED = 1
, IS_SCHOOL = 1
, IS_COMMITTEE =1
where action_page = '/Soez9006.action';

-- 帳單業者設定
update menu_item
set IS_ENABLED = 1
, IS_SCHOOL = 0
, IS_COMMITTEE =0
where action_page = '/Soez9007.action';

-- 帳單項目設定
update menu_item
set IS_ENABLED = 1
, IS_SCHOOL = 0
, IS_COMMITTEE =0
where action_page = '/Soez9008.action';

-- 上傳檔案定義
update menu_item
set IS_ENABLED = 1
, IS_SCHOOL = 0
, IS_COMMITTEE =0
where action_page = '/Soez9009.action';

GO

DELETE FROM ROLE_MENU WHERE MENU_ITEM_PK IN (SELECT MENU_ITEM_PK FROM MENU_ITEM WHERE action_page IN ('/Soez1013.action', '/Soez1014.action'));
DELETE FROM MENU_ITEM WHERE action_page = '/Soez1013.action';
DELETE FROM MENU_ITEM WHERE action_page = '/Soez1014.action';
go

SET IDENTITY_INSERT MENU_ITEM ON
GO
INSERT INTO MENU_ITEM (MENU_ITEM_PK, MENU_ID, PARENT_MENU_ITEM_PK, ACTION_PAGE, IS_ENABLED, IS_SCHOOL, IS_COMMITTEE, ORDER_NO, ACTION_BAEN, ACTION_NAME, CREATED_BY, CREATED_DATE, UPDATE_BY, UPDATE_DATE) VALUES (40, 'SOEZ5005', 4, '/Soez5006.action', 1, 0, 0, 406, 'Soez5006ActionBean', '超商代收統計查詢', 'CYBERSOFT',  GETDATE(), 'CYBERSOFT', GETDATE());
GO
SET IDENTITY_INSERT MENU_ITEM OFF
GO

-- 開啟銀行端系統管理群組
INSERT INTO ROLE_MENU (ROLE_PK, MENU_ITEM_PK) 
SELECT 1, MENU_ITEM_PK
from (
SELECT distinct MENU_ITEM_PK FROM MENU_ITEM
 WHERE action_page IN ('/Soez1011.action', '/Soez1012.action', '/Soez1015.action', '/Soez2001.action','/Soez2002.action'
, '/Soez5001.action', '/Soez5002.action', '/Soez5003.action', '/Soez5004.action', '/Soez5005.action'
, '/Soez5006.action', '/Soez9006.action', '/Soez9007.action', '/Soez9008.action', '/Soez9009.action')
OR MENU_ID IN ('SOEZ5000')
) TEMPA


ALTER TABLE CODE_TABLE ALTER COLUMN CODE_VALUE VARCHAR(20) ;
GO
INSERT INTO CODE_TABLE ( CODE_KIND,  CODE_VALUE,  CODE_NAME,  SORT_INDEX) VALUES ('WEBATMIP', '127.0.1.1', 'WEB ATM 銷帳IP 1', 1);
INSERT INTO CODE_TABLE ( CODE_KIND,  CODE_VALUE,  CODE_NAME,  SORT_INDEX) VALUES ('WEBATMIP', '10.48.10.16', 'WEB ATM 銷帳IP 3', 2);
INSERT INTO CODE_TABLE ( CODE_KIND,  CODE_VALUE,  CODE_NAME,  SORT_INDEX) VALUES ('WEBATMIP', '10.48.10.17', 'WEB ATM 銷帳IP 4', 3);
GO


INSERT INTO CODE_TABLE ( CODE_KIND,  CODE_VALUE,  CODE_NAME,  SORT_INDEX) VALUES ('PAGE_RECORD_SIZE', '20', '20', 1);
go

ALTER TABLE COMMISSION_CODE_TABLE ADD GROUP_AMOUNT Numeric(19,0)  NULL;
go
